package com.xiaren.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * @Description TODO
 * @Author xiaren
 * @Date 18/12/23 19:19
 **/
@Service
public class StudentService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void insert(String name, String sex, String address){
        jdbcTemplate.update("INSERT INTO student (name, sex, address) VALUES (?, ?, ?);", name, sex, address);
    }

    public int update(Long id, String name){
        return jdbcTemplate.update("UPDATE student SET name = ?, sex = '男', address = '湖北' WHERE id = ?", name, id);
    }

    public List<Map<String, Object>> findAll(){
        return jdbcTemplate.queryForList("select * from student");
    }

    public Map<String, Object> findByid(Long id){
        return jdbcTemplate.queryForMap("select * from student where id = ?", id);
    }

    public long insert2(String name, String sex, String address){
        KeyHolder keyHolder = new GeneratedKeyHolder();
        PreparedStatementCreator preparedStatementCreator = con -> {
            PreparedStatement ps = con.prepareStatement("INSERT INTO student (name, sex, address) VALUES (?, ?, ?);", Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, name);
            ps.setString(2, sex);
            ps.setString(3, address);
            return ps;
        };
        jdbcTemplate.update(preparedStatementCreator, keyHolder);
        return keyHolder.getKey().longValue();
    }

    public int update2(Long id, String name, String address){
        return jdbcTemplate.update("UPDATE student SET name = ?, sex = '男', address = ?  WHERE id = ?", name, address, id);
    }

    public List<Map<String, Object>> findByName(String name){
        return jdbcTemplate.queryForList("select * from student where id = ?", name);
    }

}
